"""
主页面路由
"""
from datetime import datetime
from flask import Blueprint, render_template, request, redirect, url_for
from flask_login import login_required, current_user
from sqlalchemy import func

from app.extensions import db
from app.models.customer import Customer
from app.models.staff import Staff
from app.models.negotiation import Negotiation

main_bp = Blueprint('main', __name__)

# 配置上传文件夹
UPLOAD_FOLDER = 'uploads'
ALLOWED_EXTENSIONS = {'txt', 'pdf', 'png', 'jpg', 'jpeg', 'gif', 'xls', 'xlsx', 'csv'}

def allowed_file(filename):
    return '.' in filename and filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS


@main_bp.route('/')
def index():
    if current_user.is_authenticated:
        return redirect(url_for('main.dashboard'))
    return redirect(url_for('auth.login'))


@main_bp.route('/dashboard')
@login_required
def dashboard():
    """系统主页 - 统计数据和图表"""
    # 获取统计数据
    total_customers = Customer.query.count()
    current_year_customers = Customer.query.filter(
        func.strftime('%Y', Customer.created_at) == str(datetime.now().year)
    ).count()
    
    total_staff = Staff.query.count()
    current_year_staff = Staff.query.filter(
        func.strftime('%Y', Staff.created_at) == str(datetime.now().year)
    ).count()
    
    online_negotiations = Negotiation.query.filter(
        Negotiation.category == '线上合作商谈',
        func.strftime('%Y', Negotiation.created_at) == str(datetime.now().year)
    ).count()
    
    offline_negotiations = Negotiation.query.filter(
        Negotiation.category == '线下合作商谈',
        func.strftime('%Y', Negotiation.created_at) == str(datetime.now().year)
    ).count()
    
    # 生成图表数据
    charts_data = generate_dashboard_charts()
    
    return render_template('main/dashboard.html',
                         total_customers=total_customers,
                         current_year_customers=current_year_customers,
                         total_staff=total_staff,
                         current_year_staff=current_year_staff,
                         online_negotiations=online_negotiations,
                         offline_negotiations=offline_negotiations,
                         charts_data=charts_data)


def generate_dashboard_charts():
    """生成仪表板图表数据"""
    # 获取当前年份的月度数据
    current_year = datetime.now().year
    months = list(range(1, 13))
    month_names = ['1月', '2月', '3月', '4月', '5月', '6月', 
                   '7月', '8月', '9月', '10月', '11月', '12月']
    
    # 客户数量变化
    customer_data = []
    for month in months:
        count = Customer.query.filter(
            func.strftime('%Y', Customer.created_at) == str(current_year),
            func.strftime('%m', Customer.created_at) == f'{month:02d}'
        ).count()
        customer_data.append(count)
    
    # 线上商谈次数变化
    online_data = []
    for month in months:
        count = Negotiation.query.filter(
            Negotiation.category == '线上合作商谈',
            func.strftime('%Y', Negotiation.created_at) == str(current_year),
            func.strftime('%m', Negotiation.created_at) == f'{month:02d}'
        ).count()
        online_data.append(count)
    
    # 线下商谈次数变化
    offline_data = []
    for month in months:
        count = Negotiation.query.filter(
            Negotiation.category == '线下合作商谈',
            func.strftime('%Y', Negotiation.created_at) == str(current_year),
            func.strftime('%m', Negotiation.created_at) == f'{month:02d}'
        ).count()
        offline_data.append(count)
    
    # 简化的图表数据，用于前端Chart.js渲染
    return {
        'customer_chart': {
            'labels': month_names,
            'data': customer_data,
            'title': '客户总量年度变化'
        },
        'online_chart': {
            'labels': month_names,
            'data': online_data,
            'title': '线上商谈年度变化'
        },
        'offline_chart': {
            'labels': month_names,
            'data': offline_data,
            'title': '线下商谈年度变化'
        }
    }


@main_bp.route('/customer_management')
@login_required
def customer_management():
    """客户管理页面 - 重定向到新路由"""
    return redirect(url_for('customer.customer_management'))


@main_bp.route('/add_customer', methods=['POST'])
@login_required
def add_customer():
    """添加客户"""
    try:
        name = request.form.get('name')
        industry = request.form.get('industry', '')
        contact_person = request.form.get('contact_person', '')
        contact_phone = request.form.get('contact_phone', '')
        contact_email = request.form.get('contact_email', '')
        address = request.form.get('address', '')
        description = request.form.get('description', '')
        
        customer = Customer(
            name=name,
            industry=industry,
            contact_person=contact_person,
            contact_phone=contact_phone,
            contact_email=contact_email,
            address=address,
            description=description
        )
        
        db.session.add(customer)
        db.session.commit()
        
        flash('客户添加成功！')
    except Exception as e:
        db.session.rollback()
        flash(f'添加失败：{str(e)}')
    
    return redirect(url_for('main.customer_management'))


@main_bp.route('/edit_customer/<int:customer_id>', methods=['POST'])
@login_required
def edit_customer(customer_id):
    """编辑客户"""
    try:
        customer = Customer.query.get_or_404(customer_id)
        
        customer.name = request.form.get('name')
        customer.industry = request.form.get('industry', '')
        customer.contact_person = request.form.get('contact_person', '')
        customer.contact_phone = request.form.get('contact_phone', '')
        customer.contact_email = request.form.get('contact_email', '')
        customer.address = request.form.get('address', '')
        customer.description = request.form.get('description', '')
        
        db.session.commit()
        flash('客户信息更新成功！')
    except Exception as e:
        db.session.rollback()
        flash(f'更新失败：{str(e)}')
    
    return redirect(url_for('main.customer_management'))


@main_bp.route('/delete_customer/<int:customer_id>', methods=['POST'])
@login_required
def delete_customer(customer_id):
    """删除客户"""
    try:
        customer = Customer.query.get_or_404(customer_id)
        
        # 检查是否有相关的商谈记录
        negotiations = Negotiation.query.filter_by(customer_id=customer_id).first()
        if negotiations:
            flash('无法删除：该客户存在商谈记录')
            return redirect(url_for('main.customer_management'))
        
        db.session.delete(customer)
        db.session.commit()
        flash('客户删除成功！')
    except Exception as e:
        db.session.rollback()
        flash(f'删除失败：{str(e)}')
    
    return redirect(url_for('main.customer_management'))


@main_bp.route('/export_customers')
@login_required
def export_customers():
    """导出客户数据到Excel"""
    try:
        # 创建内存中的Excel文件
        output = BytesIO()
        workbook = xlsxwriter.Workbook(output)
        worksheet = workbook.add_worksheet('客户数据')
        
        # 设置表头
        headers = ['ID', '客户名称', '所属行业', '联系人', '联系电话', '邮箱', '地址', '备注', '创建时间']
        for col, header in enumerate(headers):
            worksheet.write(0, col, header)
        
        # 写入数据
        customers = Customer.query.all()
        for row, customer in enumerate(customers, 1):
            worksheet.write(row, 0, customer.id)
            worksheet.write(row, 1, customer.name)
            worksheet.write(row, 2, customer.industry)
            worksheet.write(row, 3, customer.contact_person)
            worksheet.write(row, 4, customer.contact_phone)
            worksheet.write(row, 5, customer.contact_email)
            worksheet.write(row, 6, customer.address)
            worksheet.write(row, 7, customer.description)
            worksheet.write(row, 8, customer.created_at.strftime('%Y-%m-%d %H:%M:%S'))
        
        workbook.close()
        output.seek(0)
        
        # 发送文件
        return send_file(
            output,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            as_attachment=True,
            download_name=f'客户数据_{datetime.now().strftime("%Y%m%d_%H%M%S")}.xlsx'
        )
    
    except Exception as e:
        flash(f'导出失败：{str(e)}')
        return redirect(url_for('main.customer_management'))


@main_bp.route('/staff_management')
@login_required
def staff_management():
    """人员管理页面"""
    # 获取所有人员
    staff_list = Staff.query.all()
    
    # 获取所有客户
    customers = Customer.query.all()
    
    # 获取人员项目关系
    staff_projects = StaffProject.query.all()
    
    # 获取客户人员关系
    customer_staff_relations = CustomerStaff.query.all()
    
    return render_template('staff/staff_management.html', 
                         staff_list=staff_list,
                         customers=customers,
                         staff_projects=staff_projects,
                         customer_staff_relations=customer_staff_relations)


@main_bp.route('/add_staff', methods=['POST'])
@login_required
def add_staff():
    """添加人员"""
    try:
        name = request.form.get('name')
        position = request.form.get('position')
        phone = request.form.get('phone', '')
        email = request.form.get('email', '')
        
        staff = Staff(
            name=name,
            position=position,
            phone=phone,
            email=email
        )
        
        db.session.add(staff)
        db.session.commit()
        
        flash('人员添加成功！')
    except Exception as e:
        db.session.rollback()
        flash(f'添加失败：{str(e)}')
    
    return redirect(url_for('main.staff_management'))


@main_bp.route('/add_staff_project', methods=['POST'])
@login_required
def add_staff_project():
    """添加人员项目关系"""
    try:
        staff_id = request.form.get('staff_id')
        project_name = request.form.get('project_name')
        role = request.form.get('role')
        
        staff_project = StaffProject(
            staff_id=staff_id,
            project_name=project_name,
            role=role
        )
        
        db.session.add(staff_project)
        db.session.commit()
        
        flash('项目关系添加成功！')
    except Exception as e:
        db.session.rollback()
        flash(f'添加失败：{str(e)}')
    
    return redirect(url_for('main.staff_management'))


@main_bp.route('/delete_staff_project/<int:staff_project_id>', methods=['POST'])
@login_required
def delete_staff_project(staff_project_id):
    """删除人员项目关系"""
    try:
        staff_project = StaffProject.query.get_or_404(staff_project_id)
        db.session.delete(staff_project)
        db.session.commit()
        flash('项目关系删除成功！')
    except Exception as e:
        db.session.rollback()
        flash(f'删除失败：{str(e)}')
    
    return redirect(url_for('main.staff_management'))


@main_bp.route('/add_customer_staff_relation', methods=['POST'])
@login_required
def add_customer_staff_relation():
    """添加客户人员关系"""
    try:
        customer_id = request.form.get('customer_id')
        staff_id = request.form.get('staff_id')
        relationship_type = request.form.get('relationship_type')
        
        # 检查关系是否已存在
        existing = CustomerStaff.query.filter_by(
            customer_id=customer_id, 
            staff_id=staff_id, 
            relationship_type=relationship_type
        ).first()
        
        if existing:
            flash('该关系已存在！')
            return redirect(url_for('main.staff_management'))
        
        customer_staff = CustomerStaff(
            customer_id=customer_id,
            staff_id=staff_id,
            relationship_type=relationship_type
        )
        
        db.session.add(customer_staff)
        db.session.commit()
        
        flash('关系添加成功！')
    except Exception as e:
        db.session.rollback()
        flash(f'添加失败：{str(e)}')
    
    return redirect(url_for('main.staff_management'))


@main_bp.route('/download_template')
@login_required
def download_template():
    """下载合作商谈数据导入模板"""
    try:
        # 创建内存中的Excel文件
        output = BytesIO()
        workbook = xlsxwriter.Workbook(output)
        worksheet = workbook.add_worksheet('合作商谈模板')
        
        # 设置表头
        headers = [
            '客户名称', '商谈日期(YYYY-MM-DD)', '商谈类型(线上合作商谈/线下合作商谈)', 
            '商谈时长(分钟)', '项目负责人', '商谈内容', '合作结果', '备注'
        ]
        
        # 写入表头
        for col, header in enumerate(headers):
            worksheet.write(0, col, header)
        
        # 写入示例数据
        example_data = [
            ['示例客户A', '2024-01-15', '线上合作商谈', '60', '张三', '讨论项目合作方案', '达成初步合作意向', '后续跟进'],
            ['示例客户B', '2024-01-16', '线下合作商谈', '90', '李四', '现场考察项目情况', '签署合作协议', '项目正式启动']
        ]
        
        for row, data in enumerate(example_data, 1):
            for col, value in enumerate(data):
                worksheet.write(row, col, value)
        
        workbook.close()
        output.seek(0)
        
        return send_file(
            output,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            as_attachment=True,
            download_name='合作商谈导入模板.xlsx'
        )
    
    except Exception as e:
        flash(f'模板下载失败：{str(e)}')
        return redirect(url_for('main.negotiation_list'))


@main_bp.route('/upload_negotiations', methods=['POST'])
@login_required
def upload_negotiations():
    """批量上传合作商谈数据"""
    if 'file' not in request.files:
        flash('请选择文件')
        return redirect(url_for('main.negotiation_list'))
    
    file = request.files['file']
    if file.filename == '':
        flash('请选择文件')
        return redirect(url_for('main.negotiation_list'))
    
    if file and allowed_file(file.filename):
        try:
            # 读取Excel文件
            workbook = openpyxl.load_workbook(file)
            worksheet = workbook.active
            
            success_count = 0
            error_count = 0
            errors = []
            
            # 从第二行开始读取数据（第一行是表头）
            for row_num, row in enumerate(worksheet.iter_rows(min_row=2, values_only=True), 2):
                try:
                    if not any(row):  # 跳过空行
                        continue
                    
                    customer_name, negotiation_date, category, duration, staff_name, content, result, notes = row
                    
                    # 验证必填字段
                    if not customer_name:
                        raise ValueError("客户名称不能为空")
                    
                    # 查找客户
                    customer = Customer.query.filter_by(name=customer_name).first()
                    if not customer:
                        # 创建新客户
                        customer = Customer(name=customer_name)
                        db.session.add(customer)
                        db.session.flush()  # 获取customer.id
                    
                    # 查找负责人
                    staff = None
                    if staff_name:
                        staff = Staff.query.filter_by(name=staff_name).first()
                        if not staff:
                            # 创建新员工
                            staff = Staff(name=staff_name, position='项目负责人')
                            db.session.add(staff)
                            db.session.flush()
                    
                    # 验证商谈类型
                    if category not in ['线上合作商谈', '线下合作商谈']:
                        category = '线上合作商谈'  # 默认值
                    
                    # 处理日期
                    if isinstance(negotiation_date, str):
                        negotiation_date = datetime.strptime(negotiation_date, '%Y-%m-%d').date()
                    elif hasattr(negotiation_date, 'date'):
                        negotiation_date = negotiation_date.date()
                    else:
                        negotiation_date = datetime.now().date()
                    
                    # 处理时长
                    try:
                        duration = int(duration) if duration else 0
                    except (ValueError, TypeError):
                        duration = 0
                    
                    # 创建商谈记录
                    negotiation = Negotiation(
                        customer_id=customer.id,
                        negotiation_date=negotiation_date,
                        category=category,
                        duration=duration,
                        staff_id=staff.id if staff else None,
                        content=content or '',
                        result=result or '',
                        notes=notes or ''
                    )
                    
                    db.session.add(negotiation)
                    success_count += 1
                    
                except Exception as e:
                    error_count += 1
                    error_msg = f"第{row_num}行：{str(e)}"
                    errors.append(error_msg)
                    
                    # 记录异常数据
                    try:
                        exception_record = UploadException(
                            filename=file.filename,
                            row_number=row_num,
                            error_message=error_msg,
                            raw_data=str(row)
                        )
                        db.session.add(exception_record)
                    except:
                        pass  # 如果连异常记录都无法保存，则忽略
            
            db.session.commit()
            
            if success_count > 0:
                flash(f'成功导入 {success_count} 条记录')
            if error_count > 0:
                flash(f'导入失败 {error_count} 条记录，详情请查看异常数据')
                for error in errors[:5]:  # 只显示前5个错误
                    flash(error)
                if len(errors) > 5:
                    flash(f'还有 {len(errors) - 5} 个错误未显示...')
            
        except Exception as e:
            db.session.rollback()
            flash(f'文件处理失败：{str(e)}')
    else:
        flash('不支持的文件格式')
    
    return redirect(url_for('main.negotiation_list'))


@main_bp.route('/negotiation_list')
@login_required
def negotiation_list():
    """合作商谈列表页面"""
    # 获取查询参数
    page = request.args.get('page', 1, type=int)
    per_page = 20
    
    customer_filter = request.args.get('customer')
    category_filter = request.args.get('category')
    staff_filter = request.args.get('staff')
    date_from = request.args.get('date_from')
    date_to = request.args.get('date_to')
    
    # 构建查询
    query = Negotiation.query
    
    if customer_filter:
        query = query.join(Customer).filter(Customer.name.contains(customer_filter))
    
    if category_filter:
        query = query.filter(Negotiation.category == category_filter)
    
    if staff_filter:
        query = query.join(Staff).filter(Staff.name.contains(staff_filter))
    
    if date_from:
        try:
            date_from_obj = datetime.strptime(date_from, '%Y-%m-%d').date()
            query = query.filter(Negotiation.negotiation_date >= date_from_obj)
        except ValueError:
            pass
    
    if date_to:
        try:
            date_to_obj = datetime.strptime(date_to, '%Y-%m-%d').date()
            query = query.filter(Negotiation.negotiation_date <= date_to_obj)
        except ValueError:
            pass
    
    # 分页查询
    negotiations = query.order_by(Negotiation.created_at.desc()).paginate(
        page=page, per_page=per_page, error_out=False
    )
    
    # 获取筛选选项
    customers = Customer.query.all()
    staff_list = Staff.query.all()
    
    return render_template('negotiation/negotiation_list.html',
                         negotiations=negotiations,
                         customers=customers,
                         staff_list=staff_list,
                         current_filters={
                             'customer': customer_filter,
                             'category': category_filter,
                             'staff': staff_filter,
                             'date_from': date_from,
                             'date_to': date_to
                         })


@main_bp.route('/stats_by_staff')
@login_required
def stats_by_staff():
    """按项目负责人统计"""
    # 获取查询参数
    year = request.args.get('year', datetime.now().year, type=int)
    month = request.args.get('month', type=int)
    
    # 构建基础查询
    query = db.session.query(
        Staff.name,
        func.count(Negotiation.id).label('total_negotiations'),
        func.sum(Negotiation.duration).label('total_duration'),
        func.avg(Negotiation.duration).label('avg_duration')
    ).outerjoin(Negotiation, Staff.id == Negotiation.staff_id)
    
    # 添加年份过滤
    query = query.filter(func.strftime('%Y', Negotiation.negotiation_date) == str(year))
    
    # 添加月份过滤（如果指定）
    if month:
        query = query.filter(func.strftime('%m', Negotiation.negotiation_date) == f'{month:02d}')
    
    # 分组和排序
    stats = query.group_by(Staff.id, Staff.name).order_by(func.count(Negotiation.id).desc()).all()
    
    # 获取可选年份列表
    available_years = db.session.query(
        func.strftime('%Y', Negotiation.negotiation_date).label('year')
    ).distinct().order_by('year').all()
    available_years = [int(y.year) for y in available_years if y.year]
    
    return render_template('statistics/stats_by_staff.html',
                         stats=stats,
                         current_year=year,
                         current_month=month,
                         available_years=available_years)


@main_bp.route('/stats_by_customer')
@login_required
def stats_by_customer():
    """按客户统计"""
    # 获取查询参数
    year = request.args.get('year', datetime.now().year, type=int)
    month = request.args.get('month', type=int)
    
    # 构建基础查询
    query = db.session.query(
        Customer.name,
        Customer.industry,
        func.count(Negotiation.id).label('total_negotiations'),
        func.sum(Negotiation.duration).label('total_duration'),
        func.avg(Negotiation.duration).label('avg_duration')
    ).outerjoin(Negotiation, Customer.id == Negotiation.customer_id)
    
    # 添加年份过滤
    query = query.filter(func.strftime('%Y', Negotiation.negotiation_date) == str(year))
    
    # 添加月份过滤（如果指定）
    if month:
        query = query.filter(func.strftime('%m', Negotiation.negotiation_date) == f'{month:02d}')
    
    # 分组和排序
    stats = query.group_by(Customer.id, Customer.name, Customer.industry).order_by(func.count(Negotiation.id).desc()).all()
    
    # 获取可选年份列表
    available_years = db.session.query(
        func.strftime('%Y', Negotiation.negotiation_date).label('year')
    ).distinct().order_by('year').all()
    available_years = [int(y.year) for y in available_years if y.year]
    
    return render_template('statistics/stats_by_customer.html',
                         stats=stats,
                         current_year=year,
                         current_month=month,
                         available_years=available_years)


@main_bp.route('/stats_by_position')
@login_required
def stats_by_position():
    """按职务统计"""
    # 获取查询参数
    year = request.args.get('year', datetime.now().year, type=int)
    month = request.args.get('month', type=int)
    
    # 构建基础查询
    query = db.session.query(
        Staff.position,
        func.count(Negotiation.id).label('total_negotiations'),
        func.sum(Negotiation.duration).label('total_duration'),
        func.avg(Negotiation.duration).label('avg_duration'),
        func.count(func.distinct(Staff.id)).label('staff_count')
    ).outerjoin(Negotiation, Staff.id == Negotiation.staff_id)
    
    # 添加年份过滤
    query = query.filter(func.strftime('%Y', Negotiation.negotiation_date) == str(year))
    
    # 添加月份过滤（如果指定）
    if month:
        query = query.filter(func.strftime('%m', Negotiation.negotiation_date) == f'{month:02d}')
    
    # 分组和排序
    stats = query.group_by(Staff.position).order_by(func.count(Negotiation.id).desc()).all()
    
    # 获取可选年份列表
    available_years = db.session.query(
        func.strftime('%Y', Negotiation.negotiation_date).label('year')
    ).distinct().order_by('year').all()
    available_years = [int(y.year) for y in available_years if y.year]
    
    return render_template('statistics/stats_by_position.html',
                         stats=stats,
                         current_year=year,
                         current_month=month,
                         available_years=available_years)


@main_bp.route('/weekly_ongoing')
@login_required
def weekly_ongoing():
    """每周合作客户商谈时间"""
    # 获取查询参数
    year = request.args.get('year', datetime.now().year, type=int)
    
    # 获取指定年份的所有商谈数据，按周分组
    query = db.session.query(
        func.strftime('%W', Negotiation.negotiation_date).label('week'),
        func.count(func.distinct(Negotiation.customer_id)).label('unique_customers'),
        func.sum(Negotiation.duration).label('total_duration')
    ).filter(
        func.strftime('%Y', Negotiation.negotiation_date) == str(year)
    ).group_by(
        func.strftime('%W', Negotiation.negotiation_date)
    ).order_by('week')
    
    weekly_stats = query.all()
    
    # 转换为更易读的格式
    formatted_stats = []
    for stat in weekly_stats:
        week_num = int(stat.week) if stat.week else 0
        formatted_stats.append({
            'week': f'第{week_num}周',
            'week_number': week_num,
            'unique_customers': stat.unique_customers or 0,
            'total_duration': stat.total_duration or 0,
            'avg_duration_per_customer': round((stat.total_duration or 0) / max(stat.unique_customers or 1, 1), 2)
        })
    
    # 获取可选年份列表
    available_years = db.session.query(
        func.strftime('%Y', Negotiation.negotiation_date).label('year')
    ).distinct().order_by('year').all()
    available_years = [int(y.year) for y in available_years if y.year]
    
    return render_template('statistics/weekly_ongoing.html',
                         weekly_stats=formatted_stats,
                         current_year=year,
                         available_years=available_years)


@main_bp.route('/weekly_minmax')
@login_required
def weekly_minmax():
    """每周客户最长最短商谈记录"""
    # 获取查询参数
    year = request.args.get('year', datetime.now().year, type=int)
    
    # 获取每周的最长和最短商谈记录
    subquery = db.session.query(
        func.strftime('%W', Negotiation.negotiation_date).label('week'),
        Negotiation.customer_id,
        func.max(Negotiation.duration).label('max_duration'),
        func.min(Negotiation.duration).label('min_duration')
    ).filter(
        func.strftime('%Y', Negotiation.negotiation_date) == str(year)
    ).group_by(
        func.strftime('%W', Negotiation.negotiation_date),
        Negotiation.customer_id
    ).subquery()
    
    # 获取全局每周最长最短记录
    weekly_query = db.session.query(
        subquery.c.week,
        func.max(subquery.c.max_duration).label('week_max_duration'),
        func.min(subquery.c.min_duration).label('week_min_duration')
    ).group_by(subquery.c.week).order_by(subquery.c.week)
    
    weekly_extremes = weekly_query.all()
    
    # 获取具体的最长最短记录详情
    detailed_stats = []
    for week_stat in weekly_extremes:
        week_num = int(week_stat.week) if week_stat.week else 0
        
        # 查找该周最长商谈记录
        max_negotiation = db.session.query(
            Negotiation, Customer.name.label('customer_name'), Staff.name.label('staff_name')
        ).join(Customer).outerjoin(Staff).filter(
            func.strftime('%W', Negotiation.negotiation_date) == week_stat.week,
            func.strftime('%Y', Negotiation.negotiation_date) == str(year),
            Negotiation.duration == week_stat.week_max_duration
        ).first()
        
        # 查找该周最短商谈记录
        min_negotiation = db.session.query(
            Negotiation, Customer.name.label('customer_name'), Staff.name.label('staff_name')
        ).join(Customer).outerjoin(Staff).filter(
            func.strftime('%W', Negotiation.negotiation_date) == week_stat.week,
            func.strftime('%Y', Negotiation.negotiation_date) == str(year),
            Negotiation.duration == week_stat.week_min_duration
        ).first()
        
        detailed_stats.append({
            'week': f'第{week_num}周',
            'week_number': week_num,
            'max_record': {
                'duration': week_stat.week_max_duration,
                'customer': max_negotiation.customer_name if max_negotiation else '',
                'staff': max_negotiation.staff_name if max_negotiation else '',
                'date': max_negotiation[0].negotiation_date.strftime('%Y-%m-%d') if max_negotiation else ''
            },
            'min_record': {
                'duration': week_stat.week_min_duration,
                'customer': min_negotiation.customer_name if min_negotiation else '',
                'staff': min_negotiation.staff_name if min_negotiation else '',
                'date': min_negotiation[0].negotiation_date.strftime('%Y-%m-%d') if min_negotiation else ''
            }
        })
    
    # 获取可选年份列表
    available_years = db.session.query(
        func.strftime('%Y', Negotiation.negotiation_date).label('year')
    ).distinct().order_by('year').all()
    available_years = [int(y.year) for y in available_years if y.year]
    
    return render_template('statistics/weekly_minmax.html',
                         weekly_stats=detailed_stats,
                         current_year=year,
                         available_years=available_years)


@main_bp.route('/upload_exceptions')
@login_required
def upload_exceptions():
    """上传异常数据管理"""
    # 获取所有异常记录
    exceptions = UploadException.query.order_by(UploadException.created_at.desc()).all()
    
    return render_template('negotiation/upload_exceptions.html', exceptions=exceptions)


@main_bp.route('/view_exception_data/<int:exception_id>')
@login_required
def view_exception_data(exception_id):
    """查看异常数据详情"""
    exception = UploadException.query.get_or_404(exception_id)
    
    # 解析原始数据
    try:
        import ast
        raw_data = ast.literal_eval(exception.raw_data)
    except:
        raw_data = exception.raw_data
    
    return render_template('negotiation/view_exception_data.html', 
                         exception=exception, 
                         raw_data=raw_data)


@main_bp.route('/mark_exception_processed', methods=['POST'])
@login_required
def mark_exception_processed():
    """标记异常数据为已处理"""
    try:
        exception_id = request.form.get('exception_id')
        exception = UploadException.query.get_or_404(exception_id)
        
        exception.is_processed = True
        exception.processed_at = datetime.now()
        
        db.session.commit()
        flash('异常数据已标记为已处理')
    except Exception as e:
        db.session.rollback()
        flash(f'操作失败：{str(e)}')
    
    return redirect(url_for('main.upload_exceptions'))


@main_bp.route('/report_generation')
@login_required
def report_generation():
    """报表生成页面"""
    return render_template('reports/report_generation.html')


@main_bp.route('/generate_report/<report_type>')
@login_required
def generate_report(report_type):
    """生成并下载报表"""
    try:
        import xlsxwriter
        
        # 创建内存中的Excel文件
        output = BytesIO()
        workbook = xlsxwriter.Workbook(output)
        
        # 添加格式
        header_format = workbook.add_format({
            'bold': True,
            'bg_color': '#D3D3D3',
            'border': 1
        })
        
        data_format = workbook.add_format({
            'border': 1
        })
        
        if report_type == 'customer_summary':
            # 客户汇总报表
            worksheet = workbook.add_worksheet('客户汇总报表')
            
            # 表头
            headers = ['客户名称', '所属行业', '联系人', '联系电话', '合作商谈次数', '总商谈时长(分钟)', '平均商谈时长(分钟)', '最近商谈日期']
            for col, header in enumerate(headers):
                worksheet.write(0, col, header, header_format)
            
            # 数据查询
            customers_data = db.session.query(
                Customer.name,
                Customer.industry,
                Customer.contact_person,
                Customer.contact_phone,
                func.count(Negotiation.id).label('negotiation_count'),
                func.sum(Negotiation.duration).label('total_duration'),
                func.avg(Negotiation.duration).label('avg_duration'),
                func.max(Negotiation.negotiation_date).label('last_negotiation_date')
            ).outerjoin(Negotiation).group_by(Customer.id).all()
            
            # 写入数据
            for row, data in enumerate(customers_data, 1):
                worksheet.write(row, 0, data.name or '', data_format)
                worksheet.write(row, 1, data.industry or '', data_format)
                worksheet.write(row, 2, data.contact_person or '', data_format)
                worksheet.write(row, 3, data.contact_phone or '', data_format)
                worksheet.write(row, 4, data.negotiation_count or 0, data_format)
                worksheet.write(row, 5, data.total_duration or 0, data_format)
                worksheet.write(row, 6, round(data.avg_duration or 0, 2), data_format)
                worksheet.write(row, 7, data.last_negotiation_date.strftime('%Y-%m-%d') if data.last_negotiation_date else '', data_format)
            
            filename = f'客户汇总报表_{datetime.now().strftime("%Y%m%d_%H%M%S")}.xlsx'
        
        elif report_type == 'staff_performance':
            # 人员绩效报表
            worksheet = workbook.add_worksheet('人员绩效报表')
            
            # 表头
            headers = ['员工姓名', '职务', '负责商谈次数', '总商谈时长(分钟)', '平均商谈时长(分钟)', '客户数量', '本月商谈次数']
            for col, header in enumerate(headers):
                worksheet.write(0, col, header, header_format)
            
            # 当前月份
            current_month = datetime.now().strftime('%Y-%m')
            
            # 数据查询
            staff_data = db.session.query(
                Staff.name,
                Staff.position,
                func.count(Negotiation.id).label('total_negotiations'),
                func.sum(Negotiation.duration).label('total_duration'),
                func.avg(Negotiation.duration).label('avg_duration'),
                func.count(func.distinct(Negotiation.customer_id)).label('customer_count')
            ).outerjoin(Negotiation).group_by(Staff.id).all()
            
            # 写入数据
            for row, data in enumerate(staff_data, 1):
                # 查询本月商谈次数
                current_month_count = Negotiation.query.filter(
                    Negotiation.staff_id == Staff.query.filter_by(name=data.name).first().id,
                    func.strftime('%Y-%m', Negotiation.negotiation_date) == current_month
                ).count()
                
                worksheet.write(row, 0, data.name or '', data_format)
                worksheet.write(row, 1, data.position or '', data_format)
                worksheet.write(row, 2, data.total_negotiations or 0, data_format)
                worksheet.write(row, 3, data.total_duration or 0, data_format)
                worksheet.write(row, 4, round(data.avg_duration or 0, 2), data_format)
                worksheet.write(row, 5, data.customer_count or 0, data_format)
                worksheet.write(row, 6, current_month_count, data_format)
            
            filename = f'人员绩效报表_{datetime.now().strftime("%Y%m%d_%H%M%S")}.xlsx'
        
        elif report_type == 'monthly_analysis':
            # 月度分析报表
            worksheet = workbook.add_worksheet('月度分析报表')
            
            # 表头
            headers = ['月份', '新增客户数', '总商谈次数', '线上商谈次数', '线下商谈次数', '总商谈时长(分钟)', '平均商谈时长(分钟)']
            for col, header in enumerate(headers):
                worksheet.write(0, col, header, header_format)
            
            # 获取过去12个月的数据
            current_date = datetime.now()
            months_data = []
            
            for i in range(12):
                month_date = current_date.replace(day=1) - timedelta(days=30 * i)
                month_str = month_date.strftime('%Y-%m')
                
                # 新增客户数
                new_customers = Customer.query.filter(
                    func.strftime('%Y-%m', Customer.created_at) == month_str
                ).count()
                
                # 商谈统计
                negotiations_stats = db.session.query(
                    func.count(Negotiation.id).label('total'),
                    func.sum(case((Negotiation.category == '线上合作商谈', 1), else_=0)).label('online'),
                    func.sum(case((Negotiation.category == '线下合作商谈', 1), else_=0)).label('offline'),
                    func.sum(Negotiation.duration).label('total_duration'),
                    func.avg(Negotiation.duration).label('avg_duration')
                ).filter(
                    func.strftime('%Y-%m', Negotiation.negotiation_date) == month_str
                ).first()
                
                months_data.append({
                    'month': month_str,
                    'new_customers': new_customers,
                    'total_negotiations': negotiations_stats.total or 0,
                    'online_negotiations': negotiations_stats.online or 0,
                    'offline_negotiations': negotiations_stats.offline or 0,
                    'total_duration': negotiations_stats.total_duration or 0,
                    'avg_duration': round(negotiations_stats.avg_duration or 0, 2)
                })
            
            # 按时间排序
            months_data.reverse()
            
            # 写入数据
            for row, data in enumerate(months_data, 1):
                worksheet.write(row, 0, data['month'], data_format)
                worksheet.write(row, 1, data['new_customers'], data_format)
                worksheet.write(row, 2, data['total_negotiations'], data_format)
                worksheet.write(row, 3, data['online_negotiations'], data_format)
                worksheet.write(row, 4, data['offline_negotiations'], data_format)
                worksheet.write(row, 5, data['total_duration'], data_format)
                worksheet.write(row, 6, data['avg_duration'], data_format)
            
            filename = f'月度分析报表_{datetime.now().strftime("%Y%m%d_%H%M%S")}.xlsx'
        
        else:
            flash('不支持的报表类型')
            return redirect(url_for('main.report_generation'))
        
        workbook.close()
        output.seek(0)
        
        return send_file(
            output,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            as_attachment=True,
            download_name=filename
        )
    
    except Exception as e:
        flash(f'生成报表失败：{str(e)}')
        return redirect(url_for('main.report_generation'))


@main_bp.route('/attendance_management')
@login_required
def attendance_management():
    """考勤管理页面"""
    # 获取查询参数
    search_name = request.args.get('search_name', '')
    search_date = request.args.get('search_date', '')
    page = request.args.get('page', 1, type=int)
    per_page = 20
    
    # 构建查询
    query = Attendance.query.join(Staff)
    
    if search_name:
        query = query.filter(Staff.name.contains(search_name))
    
    if search_date:
        try:
            search_date_obj = datetime.strptime(search_date, '%Y-%m-%d').date()
            query = query.filter(Attendance.date == search_date_obj)
        except ValueError:
            pass
    
    # 分页查询
    attendances = query.order_by(Attendance.date.desc(), Staff.name).paginate(
        page=page, per_page=per_page, error_out=False
    )
    
    return render_template('attendance/attendance_management.html',
                         attendances=attendances,
                         search_name=search_name,
                         search_date=search_date)


@main_bp.route('/download_attendance_template')
@login_required
def download_attendance_template():
    """下载考勤数据导入模板"""
    try:
        # 创建内存中的Excel文件
        output = BytesIO()
        workbook = xlsxwriter.Workbook(output)
        worksheet = workbook.add_worksheet('考勤模板')
        
        # 设置表头
        headers = [
            '员工姓名', '日期(YYYY-MM-DD)', '上班时间(HH:MM)', '下班时间(HH:MM)', 
            '工作时长(小时)', '是否迟到(是/否)', '是否早退(是/否)', '考勤状态(正常/迟到/早退/缺勤)', '备注'
        ]
        
        # 写入表头
        for col, header in enumerate(headers):
            worksheet.write(0, col, header)
        
        # 写入示例数据
        example_data = [
            ['张三', '2024-01-15', '09:00', '18:00', '8', '否', '否', '正常', ''],
            ['李四', '2024-01-15', '09:15', '18:00', '7.75', '是', '否', '迟到', '迟到15分钟'],
            ['王五', '2024-01-15', '09:00', '17:30', '7.5', '否', '是', '早退', '早退30分钟']
        ]
        
        for row, data in enumerate(example_data, 1):
            for col, value in enumerate(data):
                worksheet.write(row, col, value)
        
        workbook.close()
        output.seek(0)
        
        return send_file(
            output,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            as_attachment=True,
            download_name='考勤导入模板.xlsx'
        )
    
    except Exception as e:
        flash(f'模板下载失败：{str(e)}')
        return redirect(url_for('main.attendance_management'))


@main_bp.route('/upload_attendance', methods=['POST'])
@login_required
def upload_attendance():
    """批量上传考勤数据"""
    if 'file' not in request.files:
        flash('请选择文件')
        return redirect(url_for('main.attendance_management'))
    
    file = request.files['file']
    if file.filename == '':
        flash('请选择文件')
        return redirect(url_for('main.attendance_management'))
    
    if file and allowed_file(file.filename):
        try:
            # 读取Excel文件
            workbook = openpyxl.load_workbook(file)
            worksheet = workbook.active
            
            success_count = 0
            error_count = 0
            errors = []
            
            # 从第二行开始读取数据（第一行是表头）
            for row_num, row in enumerate(worksheet.iter_rows(min_row=2, values_only=True), 2):
                try:
                    if not any(row):  # 跳过空行
                        continue
                    
                    staff_name, date_str, check_in_str, check_out_str, work_hours, is_late, is_early_leave, status, notes = row
                    
                    # 验证必填字段
                    if not staff_name:
                        raise ValueError("员工姓名不能为空")
                    
                    # 查找员工
                    staff = Staff.query.filter_by(name=staff_name).first()
                    if not staff:
                        raise ValueError(f"找不到员工：{staff_name}")
                    
                    # 处理日期
                    if isinstance(date_str, str):
                        attendance_date = datetime.strptime(date_str, '%Y-%m-%d').date()
                    elif hasattr(date_str, 'date'):
                        attendance_date = date_str.date()
                    else:
                        raise ValueError("日期格式错误")
                    
                    # 处理时间
                    check_in_time = None
                    check_out_time = None
                    
                    if check_in_str:
                        if isinstance(check_in_str, str):
                            check_in_time = datetime.strptime(check_in_str, '%H:%M').time()
                        elif hasattr(check_in_str, 'time'):
                            check_in_time = check_in_str.time()
                    
                    if check_out_str:
                        if isinstance(check_out_str, str):
                            check_out_time = datetime.strptime(check_out_str, '%H:%M').time()
                        elif hasattr(check_out_str, 'time'):
                            check_out_time = check_out_str.time()
                    
                    # 处理工作时长
                    try:
                        work_hours = float(work_hours) if work_hours else 0
                    except (ValueError, TypeError):
                        work_hours = 0
                    
                    # 处理布尔值
                    is_late = str(is_late).strip() == '是' if is_late else False
                    is_early_leave = str(is_early_leave).strip() == '是' if is_early_leave else False
                    
                    # 验证考勤状态
                    valid_statuses = ['正常', '迟到', '早退', '缺勤']
                    if status not in valid_statuses:
                        status = '正常'
                    
                    # 检查是否已存在记录
                    existing = Attendance.query.filter_by(
                        staff_id=staff.id,
                        date=attendance_date
                    ).first()
                    
                    if existing:
                        # 更新现有记录
                        existing.check_in_time = check_in_time
                        existing.check_out_time = check_out_time
                        existing.work_hours = work_hours
                        existing.is_late = is_late
                        existing.is_early_leave = is_early_leave
                        existing.status = status
                        existing.notes = notes or ''
                    else:
                        # 创建新记录
                        attendance = Attendance(
                            staff_id=staff.id,
                            date=attendance_date,
                            check_in_time=check_in_time,
                            check_out_time=check_out_time,
                            work_hours=work_hours,
                            is_late=is_late,
                            is_early_leave=is_early_leave,
                            status=status,
                            notes=notes or ''
                        )
                        db.session.add(attendance)
                    
                    success_count += 1
                    
                except Exception as e:
                    error_count += 1
                    error_msg = f"第{row_num}行：{str(e)}"
                    errors.append(error_msg)
            
            db.session.commit()
            
            if success_count > 0:
                flash(f'成功导入 {success_count} 条考勤记录')
            if error_count > 0:
                flash(f'导入失败 {error_count} 条记录')
                for error in errors[:5]:  # 只显示前5个错误
                    flash(error)
                if len(errors) > 5:
                    flash(f'还有 {len(errors) - 5} 个错误未显示...')
            
        except Exception as e:
            db.session.rollback()
            flash(f'文件处理失败：{str(e)}')
    else:
        flash('不支持的文件格式')
    
    return redirect(url_for('main.attendance_management'))


@main_bp.route('/export_attendance')
@login_required
def export_attendance():
    """导出考勤数据"""
    try:
        # 获取查询参数
        search_name = request.args.get('search_name', '')
        search_date = request.args.get('search_date', '')
        
        # 构建查询
        query = Attendance.query.join(Staff)
        
        if search_name:
            query = query.filter(Staff.name.contains(search_name))
        
        if search_date:
            try:
                search_date_obj = datetime.strptime(search_date, '%Y-%m-%d').date()
                query = query.filter(Attendance.date == search_date_obj)
            except ValueError:
                pass
        
        attendances = query.order_by(Attendance.date.desc(), Staff.name).all()
        
        # 创建内存中的Excel文件
        output = BytesIO()
        workbook = xlsxwriter.Workbook(output)
        worksheet = workbook.add_worksheet('考勤数据')
        
        # 设置表头
        headers = ['员工姓名', '日期', '上班时间', '下班时间', '工作时长(小时)', '是否迟到', '是否早退', '考勤状态', '备注']
        for col, header in enumerate(headers):
            worksheet.write(0, col, header)
        
        # 写入数据
        for row, attendance in enumerate(attendances, 1):
            worksheet.write(row, 0, attendance.staff.name)
            worksheet.write(row, 1, attendance.date.strftime('%Y-%m-%d'))
            worksheet.write(row, 2, attendance.check_in_time.strftime('%H:%M') if attendance.check_in_time else '')
            worksheet.write(row, 3, attendance.check_out_time.strftime('%H:%M') if attendance.check_out_time else '')
            worksheet.write(row, 4, attendance.work_hours)
            worksheet.write(row, 5, '是' if attendance.is_late else '否')
            worksheet.write(row, 6, '是' if attendance.is_early_leave else '否')
            worksheet.write(row, 7, attendance.status)
            worksheet.write(row, 8, attendance.notes or '')
        
        workbook.close()
        output.seek(0)
        
        # 生成文件名
        filename_parts = ['考勤数据']
        if search_name:
            filename_parts.append(f'员工{search_name}')
        if search_date:
            filename_parts.append(f'日期{search_date}')
        filename_parts.append(datetime.now().strftime('%Y%m%d_%H%M%S'))
        filename = '_'.join(filename_parts) + '.xlsx'
        
        return send_file(
            output,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            as_attachment=True,
            download_name=filename
        )
    
    except Exception as e:
        flash(f'导出失败：{str(e)}')
        return redirect(url_for('main.attendance_management'))


# 为了兼容性，添加一些case函数支持（如果SQLite没有case函数）
def case(condition, value_if_true, else_=None):
    """简化的case函数，用于SQLite查询"""
    from sqlalchemy import case
    return case((condition, value_if_true), else_=else_)